package com.bo.Dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bo.Dao.CompanyDao;
import com.bo.Entity.Company;
import com.bo.Entity.MyImage;
import com.bo.Entity.MyPage;
import com.bo.Entity.Product;
import com.bo.util.MyLobStreamingResultSetExtractor;


@Repository
public class CompanyDaoImpl implements CompanyDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private LobHandler lobHandler;
	
	

	public List<Company> login(String account , String password) {

		String sql="select id from company where account=? and password=?";
		
		RowMapper<Company> mapper = new RowMapper<Company>() {
			public Company mapRow(ResultSet rs, int rowNum) throws SQLException {
				
			  Company company=new Company();
			  company.setId(rs.getInt("id"));
			  return company;
			}
		};
		
	    return jdbcTemplate.query(sql, new Object[]{account,password}, mapper);
	     
	}

	

	public int save(Company company){
		
		String sql="insert into company(name,key_word,account,password) value (?,?,?,?)";
		
		return jdbcTemplate.update(sql,company.getName(),company.getKeyWord()
			                          ,company.getAccount(),company.getPassword());
		
	}
	
	
	

	public int checkAccount(String account){
		
		String sql="select count(id) from company where account=?";
		
		return jdbcTemplate.queryForInt(sql, new Object[]{account}); 
	}
	
	
	
	public List<Company> getCompanyList(String search,final MyPage<Company> myPage){
		
		return this.jdbcTemplate.query("select distinct * from company "
				+"where name like '%"+search+"%' or key_word like '%"+search+"%'"+"order by id", new ResultSetExtractor<List<Company>>(){

	     public List<Company> extractData(ResultSet rs) throws SQLException,DataAccessException {
				
				List<Company> result=new ArrayList<Company>();
				int count=0;
				while(rs.next()&&count<myPage.getFirstRow()+myPage.getCountPerPage()){
					
				   if(count>=myPage.getFirstRow()){
			      
                           Company cpy=new Company();
                           
                           cpy.setId(rs.getInt("id"));
                           cpy.setName(rs.getString("name"));
           				   cpy.setAddress(rs.getString("address"));
           				   cpy.setImage(new MyImage(1,rs.getString("image_name")));
           				   
                           result.add(cpy);
				   }
				   
				   count++;
			    }
				
				return result;
	       }
		});
	}
	
	

	public int count() {
		
		return this.jdbcTemplate.queryForInt("select count(*) from company");
	}
	

	

	public void getImage(final int id,final MyImage image){
		
		String sql="SELECT image FROM company WHERE id=?";
		
		jdbcTemplate.query(sql, new Object[] {id}
		           ,new MyLobStreamingResultSetExtractor(lobHandler, id, image));
	}
}
